package com.ww.word.db;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ww.core.util.Logger;
import com.ww.core.util.StringUtils;
import com.ww.core.util.TimeUtils;
import com.ww.word.entity.Study;
import com.ww.word.entity.Word;
import com.ww.word.ui.Constants;

public class StudyDbHelper extends SQLiteOpenHelper {

	private SQLiteDatabase sqlitedb;
	private static final String T_STORE = "T_STORE", T_STUDY = "T_STUDY", T_DATE = "T_DATE";
	private static final String ID_WORD = "ID_WORD", NAME_WORD = "NAME_WORD", TIME_STORY = "TIME_STORY",
			TIME_STUDY = "TIME_STUDY", SIMPLE_GROUP = "SIMPLE_GROUP", PINYIN = "PINYIN";
	private Context context;

	/**
	 * @param context Context
	 */
	public StudyDbHelper(Context context) {
		super(context, Constants.DATABASE_PATH + Constants.MY_DATABASE_NAME, null, 1);
		File dir = new File(Constants.DATABASE_PATH);
		if (!dir.exists()) {
			dir.mkdir();
		}
		this.context = context;
		SQLiteDatabase.openOrCreateDatabase(Constants.DATABASE_PATH + Constants.MY_DATABASE_NAME, null);
		sqlitedb = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(createStoreTable());
		db.execSQL(createStudyTable());
		db.execSQL(createDateTable());

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * 创建收藏表
	 * @return  sql
	 */
	private String createStoreTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_STORE
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + ID_WORD + " text");
		sql1.append("," + NAME_WORD + " text");
		sql1.append("," + SIMPLE_GROUP + " text");
		sql1.append("," + PINYIN + " text");
		sql1.append("," + TIME_STORY + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 创建学习表
	 * @return  sql
	 */
	private String createDateTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_DATE
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + TIME_STUDY + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 创建学习表
	 * @return  sql
	 */
	private String createStudyTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_STUDY
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + ID_WORD + " text");
		sql1.append("," + TIME_STUDY + " text");
		sql1.append("," + NAME_WORD + " text");
		sql1.append("," + SIMPLE_GROUP + " text");
		sql1.append("," + PINYIN + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 更新收藏(有则删除，无则添加).
	 * @param idWord 汉字id
	 * @param String 返回信息
	 */
	public String updateStore(int idWord, String nameWord, String simpleGroup, String pinyin) {
		String str = null;
		try {
			ContentValues cvD = new ContentValues();
			if (checkExsit(T_STORE, idWord)) {
				str = "取消收藏";
				sqlitedb.delete(T_STORE, ID_WORD + "=?", new String[] { idWord + "" });
			} else {
				str = "收藏";
				cvD.put(ID_WORD, idWord);
				cvD.put(NAME_WORD, nameWord);
				cvD.put(SIMPLE_GROUP, simpleGroup);
				cvD.put(PINYIN, pinyin);
				cvD.put(TIME_STORY, new Date().getTime() + "");
				sqlitedb.insert(T_STORE, null, cvD);
			}
			str += "成功";
		} catch (Exception e) {
			e.printStackTrace();
			str += "失败";
		} finally {
			sqlitedb.close();
		}
		return str;
	}

	/**
	 * 更新收藏(有则删除，无则添加).
	 * @param idWord 汉字id
	 * @param String 返回信息
	 */

	/**
	 * 保存学习信息.
	 * @param idWord 汉字id
	 */
	public void saveStudy(int idWord, String nameWord, String simpleGroup, String pinyin) {
		try {
			ContentValues cvD = new ContentValues();
			cvD.put(ID_WORD, idWord);
			cvD.put(TIME_STUDY, TimeUtils.getToday());
			cvD.put(NAME_WORD, nameWord);
			cvD.put(SIMPLE_GROUP, simpleGroup);
			cvD.put(PINYIN, pinyin);
			if (checkExsit(T_STUDY, idWord)) {
				sqlitedb.update(T_STUDY, cvD, ID_WORD + "=?", new String[] { idWord + "" });
			} else {
				sqlitedb.insert(T_STUDY, null, cvD);
			}
			saveDate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}

	/**
	 * 保存学习信息.
	 * @param idWord 汉字id
	 */
	public void saveDate() {
		try {
			ContentValues cvD = new ContentValues();
			String time = TimeUtils.getToday();
			cvD.put(TIME_STUDY, time + "");
			if (!checkDateExsit(time)) {
				sqlitedb.insert(T_DATE, null, cvD);
				Logger.info("saveDate:" + time);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}

	/**
	 * 删除学习信息.
	 */
	public void deleteStudy() {
		try {
			sqlitedb.delete(T_STORE, null, null);
			sqlitedb.delete(T_DATE, null, null);
			sqlitedb.delete(T_STUDY, null, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}

	/**
	 * 判断汉字表是否为空.	
	 * @return  boolean (true:空 false:不空)
	 */
	public boolean checkStore(int id) {
		return checkExsit(T_STORE, id);
	}

	/**
	 * 查询是否存在.	
	 * @param table 表 
	 * @param id 关键字
	 * @return  boolean  (true:存在 false:不存在)
	 */
	private boolean checkExsit(String table, int id) {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + table + " WHERE " + ID_WORD + "=" + id + "", null);
		if (cur != null) {
			bool = cur.getCount() > 0;
		}
		if (cur != null) {
			cur.close();
		}
		return bool;
	}

	/**
	 * 查询是否存在.	
	 * @param table 表 
	 * @param id 关键字
	 * @return  boolean  (true:存在 false:不存在)
	 */
	private boolean checkDateExsit(String time) {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + T_DATE + " WHERE " + TIME_STUDY + "='" + time + "'", null);
		if (cur != null) {
			bool = cur.getCount() > 0;
		}
		if (cur != null) {
			cur.close();
		}
		return bool;
	}

	/**
	 * 获取收藏列表.
	 * @return List<Word>
	 */
	public List<Word> getStoreList(String time) {
		Logger.info("getStoreList:" + time);
		List<Word> list = new ArrayList<Word>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM  " + T_STORE + " order by " + TIME_STORY + " desc  limit 0,40";
			if (StringUtils.isNotEmpty(time)) {
				sql = "SELECT * FROM " + T_STORE + " WHERE " + TIME_STORY + "<'" + time + "'  order by " + TIME_STORY
						+ " desc  limit 0,40";
			}

			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					Word w = new Word();
					w.setId(cur.getInt(1));
					w.setName(cur.getString(2));
					w.setSimple_group(cur.getString(3));
					w.setPinyin(cur.getString(4));
					w.setTime(cur.getString(5));
					list.add(w);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}

	/**
	 * 获取学习列表.
	 * @return List<Study>
	 */
	public List<Study> getHistoryList() {
		List<Word> wordList = new ArrayList<Word>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_STUDY;
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					Word w = new Word();
					Logger.info("cur.getString(2):" + cur.getString(2));
					w.setId(cur.getInt(1));
					w.setTime(cur.getString(2));
					w.setName(cur.getString(3));
					w.setSimple_group(cur.getString(4));
					w.setPinyin(cur.getString(5));
					w.setStore(checkStore(cur.getInt(1)));
					wordList.add(w);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return checkWorkList(wordList);
	}

	/**
	 * 获取学习列表.
	 * @return List<Study>
	 */
	public List<Word> getHistoryList(String time) {
		List<Word> wordList = new ArrayList<Word>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_STUDY + " WHERE " + TIME_STUDY + "=?  ORDER BY ID_WORD DESC";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, new String[] { time });
			if (cur != null) {
				while (cur.moveToNext()) {
					Word w = new Word();
					Logger.info("cur.getString(2):" + cur.getString(2));
					w.setId(cur.getInt(1));
					w.setTime(cur.getString(2));
					w.setName(cur.getString(3));
					w.setSimple_group(cur.getString(4));
					w.setPinyin(cur.getString(5));
					w.setStore(checkStore(cur.getInt(1)));
					wordList.add(w);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return wordList;
	}

	/**
	 * 处理最近学习的7天.
	 * @param time 截止时间
	 * @return 时间列表
	 */
	public List<String> getHistoryTime() {
		List<String> wordList = new ArrayList<String>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_DATE + "  ORDER BY TIME_STUDY DESC ";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					Logger.info("getTime:" + cur.getString(1));
					wordList.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return wordList;
	}

	/**
	 * 处理最近学习的7天.
	 * @param time 截止时间
	 * @return 时间列表
	 */
	public List<String> getTime(String time) {
		List<String> wordList = new ArrayList<String>();
		Cursor cur = null;
		if (time == null) {
			time = TimeUtils.getToday();
		}
		try {
			String sql = "SELECT * FROM " + T_DATE + " WHERE " + TIME_STUDY + "<? ORDER BY TIME_STUDY DESC limit 0,7";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, new String[] { time });
			if (cur != null) {
				while (cur.moveToNext()) {
					Logger.info("getTime:" + cur.getString(1));
					wordList.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return wordList;
	}

	/**
	 * 处理学习列表的时间问题
	 * @param appExams
	 * @return
	 */
	private List<Study> checkWorkList(List<Word> wordList) {
		List<Study> list = new ArrayList<Study>();
		for (int i = 0; i < wordList.size(); i++) {
			Word word = wordList.get(i);
			Logger.info("word:" + word.getName());
			boolean isNew = false;
			if (list.size() == 0) {
				isNew = false;
			} else {
				for (int j = 0; j < list.size(); j++) {
					if (TimeUtils.formatDateTime(list.get(j).getTime())
							.equals(TimeUtils.formatDateTime(word.getTime()))) {
						isNew = true;
					}
				}
			}
			if (!isNew) {
				Study infos = new Study();
				infos.setTime(word.getTime());
				list.add(infos);
			}
		}
		for (int i = 0; i < list.size(); i++) {
			List<Word> infoList = new ArrayList<Word>();
			for (int j = 0; j < wordList.size(); j++) {
				Word info = wordList.get(j);
				if (TimeUtils.formatDateTime(list.get(i).getTime()).equals(TimeUtils.formatDateTime(info.getTime()))) {
					infoList.add(info);
				}
			}
			list.get(i).setList(infoList);

		}

		return list;
	}

	public Study getReviewList(String time) {
		Study s = new Study();
		if (time == null) {
			time = getLastStudyDate();
		}
		List<Word> list = getHistoryList(time);
		s.setList(list);
		return s;
	}

	public String getLastStudyDate() {
		String str = "0";
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_DATE + " ORDER BY TIME_STUDY DESC limit 0,1";
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					str = cur.getString(1);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return str;
	}

	public String getLastStudyWord() {
		String str = "0";
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_STUDY + " ORDER BY TIME_STUDY DESC limit 0,1";
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					str = cur.getString(1);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return str;
	}

}
